import re
import json
import time
import requests
import pymysql

config = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "mydb",
    "charset": "utf8mb4"
}
conn = pymysql.connect(**config)
cur = conn.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS report_db (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(512) NOT NULL,
    url VARCHAR(512) NOT NULL,
    org VARCHAR(128),
    pub_date DATE
) CHARSET=utf8mb4;
""")

api_tpl = (
    "https://reportapi.eastmoney.com/report/list?cb=datatable1094161&industryCode=1033&pageSize=50&industry=*&rating=*&ratingChange=*&beginTime=2023-08-17&endTime=2025-08-17&pageNo={pn}&fields=&qType=1&orgCode=&rcode=&_=1755426057318"
)

insert_sql = ("INSERT INTO report_db (title,url,org,pub_date)"
              "VALUES(%s,%s,%s,%s)")

for pn in range(1, 36):
    j = requests.get(api_tpl.format(pn=pn),
                     headers={
                         "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36 Edg/139.0.0.0",
                         "Referer": "https://data.eastmoney.com/report/ind",
                         "sec-ch-ua-platform": "Windows"
                     })

    json_str = re.search(r'datatable\d+\((.*)\)$', j.text, re.S).group(1)

    data = json.loads(json_str)
    result = data["data"]

    values = []
    for it in result:
        values.append((
            it['title'].strip(),
            f"https://data.eastmoney.com/report/zw_industry.jshtml?infocode={it['infoCode']}",
            it['orgSName'],
            it['publishDate'][:10]
        ))
    cur.executemany(insert_sql, values)
    conn.commit()
    print(f"has been finsh page{pn},insert value{len(values)}")
    time.sleep(0.2)

cur.close()
conn.close()
print("all finish")
